/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright (c) 2013-2014 jfinal app. jfapp Group.
 */

package app.models.member;

import app.Const;
import app.dtos.status.MemberStatus;
import com.github.sog.annotation.TableBind;
import com.github.sog.config.StringPool;
import com.github.sog.controller.datatables.core.ColumnDef;
import com.github.sog.controller.datatables.core.DatatablesCriterias;
import com.github.sog.kit.date.DateProvider;
import com.github.sog.kit.lang.ArithKit;
import com.github.sog.libs.AppFunc;
import com.github.sog.plugin.sqlinxml.SqlKit;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.jfinal.plugin.activerecord.*;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * The table oll_memeber mapping model.
 * </p>
 */
@TableBind(tableName = "ol_member")
public class Member extends Model<Member> {

    /**
     * The public dao.
     */
    public static final Member dao = new Member();

    private static final long serialVersionUID = 8291546040477122102L;

    public List<Member> list() {
        return find(SqlKit.sql("member.list"));
    }

    public Page<Member> datatable(DatatablesCriterias criterias) {

        int pageSize = criterias.getDisplaySize();
        int start = criterias.getDisplayStart() / pageSize + 1;
        final List<ColumnDef> sortingColumnDefs = criterias.getSortingColumnDefs();
        String where = " FROM ol_member m LEFT JOIN ol_member_rank r on r.id = m.rank WHERE status =? ";
        if (sortingColumnDefs != null && !sortingColumnDefs.isEmpty()) {
            StringBuilder orderBy = new StringBuilder();
            for (ColumnDef sortingColumnDef : sortingColumnDefs)
                if (sortingColumnDef.isSortable()) {
                    orderBy.append(sortingColumnDef.getName()).append(StringPool.SPACE).append(sortingColumnDef.getSortDirection().toString());
                }
            final String byColumns = orderBy.toString();
            if (!Strings.isNullOrEmpty(byColumns)) {
                where += " ORDER BY " + byColumns;
            }
        }
        if (!where.contains("ORDER")) {
            where += " ORDER BY card_no";
        }

        return paginate(start, pageSize, SqlKit.sql("member.columns"), where, MemberStatus.OFFICIAL);
    }

    public void changeStatus(long id, byte status) {

        Member member = new Member();
        member.set(AppFunc.TABLE_PK_COLUMN, id);
        member.set(Const.COLUMN_STATUS, status);
        member.update();

    }

    public Member findByWxNo(String wxno) {
        return findFirst(SqlKit.sql("member.findByWxNo"), wxno);
    }

    public int batch_import(List<Object[]> members) {
        final int size = members.size();
        Object[][] params = new Object[size][];
        for (int i = 0; i < size; i++) {
            params[i] = members.get(i);
        }
        int[] val = Db.batch(SqlKit.sql("member.batch_import"), params, 2000);
        return 0;
    }

    /**
     * 根据会员卡号和手机查询用户
     *
     * @param cardId 会员卡号
     * @param mobno  手机
     * @return 会员信息
     */
    public Member findByCarIdAndMno(String cardId, String mobno) {
        return dao.findFirst(SqlKit.sql("member.findByCarIdAndMno"), cardId, mobno);
    }

    /**
     * @param mid    会员id
     * @param type   增加/减少类型 true 增加 false 减少
     * @param itr    积分数值
     * @param reason 原因
     * @return true, 操作成功； false， 操作失败
     */
    public boolean optIntegral(long mid, boolean type, Double itr, String reason) {
        final Member member = findById(mid);

        final Date currentDate = DateProvider.DEFAULT.getCurrentDate();
        //final Double integral = member.getDouble("integral");
        final Double integral = member.getDouble("surplus");
        final Double add_integral_value = member.getDouble("add_integral_value");
        final MemberIntegralRecord mr = new MemberIntegralRecord()
                .set("member", mid)
                .set("integral", itr)
                .set("subtraction", type)
                .set("record_time", currentDate)
                .set("create_time", currentDate)
                .set("old_integral", integral)
                .set("status", false)
                .set("resume", reason);
        // Operate without real points, just operate the remaining points
        if (!type) {
            member.set("surplus", ArithKit.sub(integral, itr));
        } else {
            member.set("add_integral_value", ArithKit.sub(add_integral_value, itr));
        }

        return Db.tx(new IAtom() {
            @Override
            public boolean run() throws SQLException {
                boolean update = member.update();
                boolean save = mr.save();
                return update && save;
            }
        });
    }

    /**
     * @param mid    会员id
     * @param type   增加/减少类型 true 增加 false 减少
     * @param itr    货币数值
     * @param reason 原因
     * @return true, 操作成功； false， 操作失败
     */
    public boolean optCurrency(long mid, boolean type, double itr, String reason) {
        final Member member = findById(mid);

        final Double currency = member.getDouble("currency");
        final Date currentDate = DateProvider.DEFAULT.getCurrentDate();
        final MemberCurrencyRecord mr = new MemberCurrencyRecord()
                .set("member", mid)
                .set("currency", itr)
                .set("subtraction", type)
                .set("record_time", currentDate)
                .set("create_time", currentDate)
                .set("old_currency", currency)
                .set("resume", reason);
        member.set("currency", type ? ArithKit.add(currency, itr) : ArithKit.sub(currency, itr));

        return Db.tx(new IAtom() {
            @Override
            public boolean run() throws SQLException {
                boolean update = member.update();
                boolean save = mr.save();
                return update && save;
            }
        });
    }

    public Page<Member> customer_members(DatatablesCriterias criterias, Member record) {

        int pageSize = criterias.getDisplaySize();
        int start = criterias.getDisplayStart() / pageSize + 1;
        final List<ColumnDef> sortingColumnDefs = criterias.getSortingColumnDefs();
        final StringBuilder sql = new StringBuilder(" FROM ol_member m LEFT JOIN ol_member_rank r on r.id = m.rank WHERE status =? ");
        final List<Object> params = Lists.newArrayList();
        params.add(MemberStatus.OFFICIAL);
        if (record != null && !record.getAttrs().isEmpty()) {
            String nationality = record.getStr("nationality");
            if (!Strings.isNullOrEmpty(nationality)) {
                sql.append("AND m.nationality =? ");
                params.add(nationality);
            }
            String name = record.getStr("name");
            if (!Strings.isNullOrEmpty(name)) {
                sql.append("AND m.name = ? ");
                params.add(name);
            }
            String id_card = record.getStr("id_card");
            if (!Strings.isNullOrEmpty(id_card)) {
                sql.append("AND m.id_card = ? ");
                params.add(id_card);
            }
            String card_no = record.getStr("card_no");
            if (!Strings.isNullOrEmpty(card_no)) {
                sql.append("AND m.card_no = ? ");
                params.add(card_no);
            }
            String phone = record.getStr("phone");
            if (!Strings.isNullOrEmpty(phone)) {
                sql.append("AND m.phone = ? ");
                params.add(phone);
            }
            String postcode = record.getStr("postcode");
            if (!Strings.isNullOrEmpty(postcode)) {
                sql.append("AND m.postcode = ? ");
                params.add(postcode);
            }
            Boolean gender = record.getBoolean("gender");
            if (gender != null) {
                sql.append("AND m.gender = ? ");
                params.add(gender);
            }
        }
        if (params.size() < 2) {
            return null;
        }
        if (sortingColumnDefs != null && !sortingColumnDefs.isEmpty()) {
            StringBuilder orderBy = new StringBuilder();
            for (ColumnDef sortingColumnDef : sortingColumnDefs)
                if (sortingColumnDef.isSortable()) {
                    orderBy.append(sortingColumnDef.getName()).append(StringPool.SPACE).append(sortingColumnDef.getSortDirection().toString());
                }
            final String byColumns = orderBy.toString();
            if (!Strings.isNullOrEmpty(byColumns)) {
                sql.append(" ORDER BY ").append(byColumns);
            }
        }
        if (!StringUtils.contains(sql, "ORDER")) {
            sql.append(" ORDER BY card_no");
        }

        return paginate(start, pageSize, SqlKit.sql("member.customer_columns"), sql.toString(), params.toArray());
    }

    public boolean exist_card_no(String card_no, long id) {
        Member member = findFirst(SqlKit.sql("member.checkCardNo"), card_no, id);
        return member.getNumber("num").intValue() < 1;
    }

    public Member findByCardNo(String card_no) {
        return findFirst(SqlKit.sql("member.findByCardNo"), card_no);
    }

    public Page<Record> findPageByRegister(boolean register, Member member, DatatablesCriterias criterias) {
        int pageSize = criterias.getDisplaySize();
        int start = criterias.getDisplayStart() / pageSize + 1;

        final List<Object> params = Lists.newArrayList();
        params.add(register);

        final StringBuilder sql = new StringBuilder(SqlKit.sql("member.findPageByRegister_where"));

        if (member != null) {
            String name = member.getStr("name");
            if (!Strings.isNullOrEmpty(name)) {
                sql.append(" AND name like ? ");
                params.add(StringPool.PERCENT + name + StringPool.PERCENT);
            }

            String card_no = member.getStr("card_no");
            if (!Strings.isNullOrEmpty(card_no)) {
                sql.append(" AND card_no like ? ");
                params.add(StringPool.PERCENT + card_no + StringPool.PERCENT);
            }

            String id_card = member.getStr("id_card");
            if (!Strings.isNullOrEmpty(id_card)) {
                sql.append(" AND id_card like ? ");
                params.add(StringPool.PERCENT + id_card + StringPool.PERCENT);
            }

            String phone = member.getStr("phone");
            if (!Strings.isNullOrEmpty(phone)) {
                sql.append(" AND phone like ? ");
                params.add(StringPool.PERCENT + phone + StringPool.PERCENT);
            }
        }

        final List<ColumnDef> sortingColumnDefs = criterias.getSortingColumnDefs();
        if (sortingColumnDefs != null && !sortingColumnDefs.isEmpty()) {
            StringBuilder orderBy = new StringBuilder();
            for (ColumnDef sortingColumnDef : sortingColumnDefs)
                if (sortingColumnDef.isSortable()) {
                    orderBy.append(sortingColumnDef.getName()).append(StringPool.SPACE).append(sortingColumnDef.getSortDirection().toString());
                }
            final String byColumns = orderBy.toString();
            if (!Strings.isNullOrEmpty(byColumns)) {
                sql.append(" ORDER BY ").append(byColumns);
            }
        }
        if (!StringUtils.contains(sql, "ORDER")) {
            sql.append(" ORDER BY entering");
        }

        return Db.paginate(start, pageSize, SqlKit.sql("member.findPageByRegister_columns"), sql.toString(), params.toArray());
    }

    public boolean currencyExchangeIntegral(final Member member, double currency, double integral) {
        if (member == null) {
            return false;
        }

        double curCurrency = member.getDouble("currency");

        double oldIntegral = member.getDouble("integral");
        double curIntegral = member.getDouble("add_integral_value");

        Date currentDate = new Date();
        Object mid = member.get("id");
        //记录中南币扣减
        final MemberCurrencyRecord mcr = new MemberCurrencyRecord()
                .set("member", mid)
                .set("currency", currency)
                .set("subtraction", false)
                .set("record_time", currentDate)
                .set("create_time", currentDate)
                .set("old_currency", currency)
                .set("resume", Const.EXCHANGE_TO_INTEGRAL);
        member.set("currency", ArithKit.sub(curCurrency, currency));

        //记录积分增加
        final MemberIntegralRecord mir = new MemberIntegralRecord()
                .set("member", mid)
                .set("integral", integral)
                .set("subtraction", true)
                .set("record_time", currentDate)
                .set("create_time", currentDate)
                .set("old_integral", oldIntegral)
                .set("status", false)
                .set("resume", Const.EXCHANGE_FROM_CURRENCY);

        member.set("add_integral_value", ArithKit.add(curIntegral, integral));

        return Db.tx(new IAtom() {
            @Override
            public boolean run() throws SQLException {
                boolean update = member.update();
                boolean save1 = mcr.save();
                boolean save2 = mir.save();
                return update && save1 && save2;
            }
        });
    }
}